# coding=utf-8
import json

import pandas as pd

from src.api.response.R import R
from src.db.dbhelper import query_sql_by_pandas, execute_sql, pandas_to_sql
from src.db.flaskdb import db
from src.model.dynamic_remain_material import DynamicRemainMaterial


def update_plan_state(plan_id, state, msg=None):
    remark = ""
    if msg is not None:
        remark += msg
    sql = f"update material_plan set is_finish = {state}, remark ='{remark}' where id = {plan_id}"
    return execute_sql(sql)


def execute_plan(*args, **kwargs):
    request_body = kwargs.get('request_body')
    current_app = request_body.get("current_object")
    plan = request_body.get("plan")
    plan_id = plan.id
    plan_type = plan.plan_type
    r = R()
    with current_app.app_context():
        try:
            update_plan_state(plan_id, 1)
            # 获取型号
            product_sql = "select product_model, product_name, product_level, qd, jd from product_model_td pmt where is_del = 0"
            all_products_df = query_sql_by_pandas(
                product_sql)  # ProductModelTd.query.filter(ProductModelTd.is_del == 0).all()
            # 获取资产
            material_sql = "select material_id , material_name , material_quantity from material_type_quantity mtq where mtq.is_del  = 0"
            all_materials_df = query_sql_by_pandas(material_sql)
            # 将资产转换为字典方便后续使用
            material_all_dict_source = dict(zip(all_materials_df["material_id"], all_materials_df["material_quantity"]))
            material_all_dict = material_all_dict_source.copy()
            source_material_dict = material_all_dict.copy()
            use_material_dict = {}
            for key, value in material_all_dict.items():
                use_material_dict.setdefault(key, 0)
            # 获取所有的型号关系
            relation_sql = f"""
                            select
                                product_model , 
                                relation , 
                                scale  
                                from product_model_connect 
                                pmc where  is_del=0
                            """
            all_relations_df = query_sql_by_pandas(relation_sql)
            # 获取型号关系 生成新表
            # columns = ["型号","OC","IC"]
            material_ids = all_materials_df["material_id"].tolist()
            # material_names = all_materials_df["material_name"].tolist()
            columns = ["product_model"] + material_ids
            execute_total = 0
            # 按照级别进行
            # 获取战略型号战略级别
            level_sql = " select val as id, name  from sys_dic_type where dic_type = '型号级别' and is_del  = 0 "
            level_df = query_sql_by_pandas(level_sql)
            # 结果记录
            material_plan_other_df = pd.DataFrame(columns=["plan_id", "plan_name", "remark"])
            material_plan_other_df["plan_id"] = [plan_id, plan_id, plan_id]
            material_plan_other_df["plan_name"] = [plan.plan_name, plan.plan_name, plan.plan_name]
            material_plan_other_df["remark_type"] = ["剩余", "已使用", "总量"]
            # 总结果容器
            result_df_list = []
            if plan_type == 0:
                # Multiple Max
                for level in level_df["id"].tolist():
                    # print(f"当前级别:{level}")
                    level = int(level)
                    # 根据型号级别 创建结果 dataframe容器
                    filter_df = all_products_df[all_products_df["product_level"] == level]
                    # print("---------------")
                    if filter_df.empty:
                        # print(f"级别:{level}没型号数据")
                        continue
                    result_df = pd.DataFrame(columns=columns)
                    result_df["product_model"] = filter_df["product_model"].tolist()
                    result_df["finish"] = 0
                    result_df["total"] = 0
                    for material_name in material_ids:
                        result_df[material_name] = 0
                    while True:
                        # 如果所有的材料都已经不能再建造新的产品后 就结束
                        finish_df = result_df[result_df["finish"] == 0]
                        # print(finish_df)
                        if finish_df.empty:
                            break
                        for idx, row in result_df.iterrows():
                            execute_total += 1
                            product_model = row["product_model"]
                            total = row["total"]
                            finish = row["finish"]
                            if finish == 1:
                                continue
                            # 获取当前型号所需的材料组成
                            relation_detail_df = all_relations_df.query(f" product_model=='{product_model}' ")
                            is_construct = True
                            if relation_detail_df.empty:
                                is_construct = False
                            # 判断材料是否齐全
                            for idx2, row2 in relation_detail_df.iterrows():
                                relation_id = row2["relation"]
                                scale = row2["scale"]
                                # 分解所需材料
                                quantity = material_all_dict.get(relation_id)
                                if scale == 0:
                                    # 如果建造材料为0 证明当前型号未绑定材料
                                    is_construct = False
                                    break
                                if quantity - scale >= 0:
                                    # 材料充足
                                    continue
                                else:
                                    # print(f"型号:{product_model}原料不足")
                                    # 原料不足
                                    is_construct = False
                                    break
                            if not is_construct:
                                # 原料不足
                                # 在对应的位置上标注
                                # print("材料不足", product_model)
                                result_df.loc[idx, "finish"] = 1
                                # print(result_df)
                            else:
                                # 原料充足
                                # 减去构造所需原材料
                                for idx2, row2 in relation_detail_df.iterrows():
                                    relation_id = row2["relation"]
                                    scale = row2["scale"]
                                    use_scale = use_material_dict.get(relation_id)
                                    quantity = material_all_dict.get(relation_id)
                                    quantity -= scale
                                    # 记录使用量
                                    use_scale += scale
                                    material_all_dict[relation_id] = quantity
                                    old_scale = row[relation_id]
                                    new_scale = old_scale + scale
                                    result_df.loc[idx, relation_id] = new_scale
                                    # 存储使用量
                                    use_material_dict[relation_id] = use_scale
                                # 新增产品
                                total += 1
                                result_df.loc[idx, "total"] = total
                            # if execute_total % 500 == 0:
                            #     print(material_all_dict)
                            # print(f"当前次数:{execute_total}")
                            # print(result_df)
                    result_df_list.append(result_df.copy())
            if plan_type == 1:
                # Single Max
                # 直接循环所有的型号
                # print(f"当前类型: Multiple Max")
                # 根据型号级别 创建结果 dataframe容器
                filter_df = all_products_df.copy()
                result_df = pd.DataFrame(columns=columns)
                result_df["product_model"] = filter_df["product_model"].tolist()
                result_df["finish"] = 0
                result_df["total"] = 0
                for material_name in material_ids:
                    result_df[material_name] = 0
                for idx, row in result_df.iterrows():
                    is_construct = True
                    product_model = row["product_model"]
                    total = row["total"]
                    finish = row["finish"]
                    # 每次型号不一样的时候 重置材料
                    material_all_dict = material_all_dict_source.copy()
                    remain_dic = {}
                    # 获取当前型号所需的材料组成
                    relation_detail_df = all_relations_df.query(f" product_model=='{product_model}' ")
                    if relation_detail_df.empty:
                        is_construct = False
                    for idx3, row3 in relation_detail_df.iterrows():
                        relation_id_i3 = row3["relation"]
                        scale_i3 = row3["scale"]
                        remain_dic.setdefault(relation_id_i3, 0)
                    while is_construct:
                        execute_total += 1
                        if finish == 1:
                            break
                        # 判断材料是否齐全
                        for idx2, row2 in relation_detail_df.iterrows():
                            relation_id = row2["relation"]
                            scale = row2["scale"]
                            # 分解所需材料
                            quantity = material_all_dict.get(relation_id)
                            if scale == 0:
                                # 如果建造材料为0 证明当前型号未绑定材料
                                is_construct = False
                                break
                            if quantity - scale >= 0:
                                # 材料充足
                                continue
                            else:
                                print(f"型号:{product_model}原料不足")
                                # 原料不足
                                is_construct = False
                                break
                        if not is_construct:
                            # 原料不足
                            # 在对应的位置上标注
                            # print("材料不足", product_model)
                            result_df.loc[idx, "finish"] = 1
                            # print(result_df)
                        else:
                            # 原料充足
                            # 减去构造所需原材料
                            for idx2, row2 in relation_detail_df.iterrows():
                                relation_id = row2["relation"]
                                scale = row2["scale"]
                                use_scale = use_material_dict.get(relation_id)
                                quantity = material_all_dict.get(relation_id)
                                quantity -= scale
                                # 记录使用量
                                use_scale += scale
                                material_all_dict[relation_id] = quantity
                                # old_scale = row[relation_id]
                                old_scale = remain_dic.get(relation_id)
                                # remain_dic.setdefault(relation_id, old_scale)
                                new_scale = old_scale + scale
                                remain_dic[relation_id] = new_scale
                                # result_df.loc[idx, relation_id] = new_scale
                                # 存储使用量
                                use_material_dict[relation_id] = use_scale
                            # 新增产品
                            total += 1
                        # if execute_total % 500 == 0:
                        # print(material_all_dict)
                        # print(f"当前次数:{execute_total}")
                        # print(result_df)
                    for i, sc in remain_dic.items():
                        result_df.loc[idx, i] = sc
                    result_df.loc[idx, "total"] = total
                result_df_list.append(result_df.copy())
            update_plan_state(plan_id, 2)
            if len(result_df_list) > 0:
                result_df = pd.concat(result_df_list)
            # 执行结束
            result_df["plan_id"] = plan_id
            result_df["plan_name"] = plan.plan_name
            pandas_to_sql(df=result_df, table_name=f"result_{plan_id}", if_exists="replace")
            if plan_type == 0:
                other_material_dic = {}
                other_use_material_dict = {}
                other_source_material_dict = {}
                for key, val in material_all_dict.items():
                    se_df = all_materials_df.query(f"material_id == {key} ")
                    material_name = se_df.iloc[0]["material_name"]
                    other_material_dic.setdefault(material_name, val)

                    use_val = use_material_dict.get(key)
                    other_use_material_dict.setdefault(material_name, use_val)

                    source_val = source_material_dict.get(key)
                    other_source_material_dict.setdefault(material_name, source_val)
                # print("执行结束")
                material_plan_other_df["remark"] = [json.dumps(other_material_dic), json.dumps(other_use_material_dict),
                                                    json.dumps(other_source_material_dict)]
                del_sql = f"delete from material_plan_other where plan_id = {plan_id} "
                execute_sql(del_sql)
                pandas_to_sql(material_plan_other_df, "material_plan_other", if_exists="append")
        except Exception as e:
            print(e)
            update_plan_state(plan_id, 3, str(e))
            db.session.commit()
    # print("执行结束")
    return r


def execute_dynamic_plan(*args, **kwargs):
    """动态执行任务"""
    request_body = kwargs.get('request_body')
    current_app = request_body.get("current_object")
    plan = request_body.get("plan")
    plan_id = plan.id
    product_models = request_body.get("product_models")
    plan_type = plan.plan_type
    r = R()
    with current_app.app_context():
        try:
            update_plan_state(plan_id, 1, msg="动态执行...开始")
            e_sort = 1
            # 获取旧的执行信息
            try:
                old_sql = f"select * from result_{plan_id} "
                old_df = query_sql_by_pandas(old_sql)
                if not old_df.empty:
                    e_sort = old_df["e_sort"].max()
                    e_sort += 1
            except Exception as e:
                print("还未执行过...", e)
                old_df = None
                e_sort = 1
            # 获取型号
            all_products_df = pd.DataFrame(product_models, columns=["product_model"])
            # 获取资产
            dynamic_remain_mo = DynamicRemainMaterial.query.filter(DynamicRemainMaterial.plan_id == plan_id).first()
            material_remain_str = dynamic_remain_mo.material_remain
            execute_product_str = dynamic_remain_mo.execute_product
            material_all_dict_source = json.loads(material_remain_str)
            new_material_all_dict_source = {}
            for k, v in material_all_dict_source.items():
                # material_all_dict_source[int(k)] = v
                new_material_all_dict_source[int(k)] = v
            material_all_dict = new_material_all_dict_source.copy()
            source_material_dict = material_all_dict.copy()
            use_material_dict = {}
            material_columns = []
            for key, value in material_all_dict.items():
                use_material_dict.setdefault(key, 0)
                material_columns.append(key)
            # 获取所有的型号关系
            relation_sql = f"""
                                        select
                                            product_model , 
                                            relation , 
                                            scale  
                                            from product_model_connect 
                                            pmc where  is_del=0
                                        """
            all_relations_df = query_sql_by_pandas(relation_sql)
            # 获取型号关系 生成新表
            # columns = ["型号","OC","IC"]
            columns = ["product_model"] + material_columns
            execute_total = 0
            # 循环型号 进行计算
            for idx, row in all_products_df.iterrows():
                product_model = row["product_model"]
            result_df = pd.DataFrame(columns=columns)
            result_df["product_model"] = all_products_df["product_model"].tolist()
            result_df["finish"] = 0
            result_df["total"] = 0
            result_df_list = []
            for material_name in material_columns:
                result_df[material_name] = 0
            while True:
                # 如果所有的材料都已经不能再建造新的产品后 就结束
                finish_df = result_df[result_df["finish"] == 0]
                if finish_df.empty:
                    break
                for idx, row in result_df.iterrows():
                    execute_total += 1
                    product_model = row["product_model"]
                    total = row["total"]
                    finish = row["finish"]
                    is_construct = True
                    signal_use_material = {}
                    # 用于记录使用的材料
                    for material_id in material_columns:
                        # 初始化材料使用为0
                        signal_use_material.setdefault(material_id, 0)
                    while is_construct:
                        # 获取当前型号所需的材料组成
                        relation_detail_df = all_relations_df.query(f" product_model=='{product_model}' ")
                        if relation_detail_df.empty:
                            is_construct = False
                        # 判断材料是否齐全
                        for idx2, row2 in relation_detail_df.iterrows():
                            relation_id = row2["relation"]
                            scale = row2["scale"]
                            # 分解所需材料
                            quantity = material_all_dict.get(relation_id)
                            if scale == 0:
                                # 如果建造材料为0 证明当前型号未绑定材料
                                is_construct = False
                                break
                            if quantity - scale >= 0:
                                # 材料充足
                                continue
                            else:
                                # print(f"型号:{product_model}原料不足")
                                # 原料不足
                                is_construct = False
                                break
                        if not is_construct:
                            # 原料不足
                            # 在对应的位置上标注
                            print("材料不足", product_model)
                            result_df.loc[idx, "finish"] = 1
                            # print(result_df)
                        else:
                            # 原料充足
                            # 减去构造所需原材料
                            for idx2, row2 in relation_detail_df.iterrows():
                                relation_id = row2["relation"]
                                scale = row2["scale"]
                                use_scale = signal_use_material.get(relation_id)
                                quantity = material_all_dict.get(relation_id)
                                quantity -= scale
                                # 记录使用量
                                use_scale += scale
                                material_all_dict[relation_id] = quantity
                                # 记录使用总量
                                signal_use_material[relation_id] = use_scale
                                # result_df.loc[idx, relation_id] = new_scale
                                # 存储使用量
                                use_material_dict[relation_id] = use_scale
                            # 新增产品
                            total += 1
                    result_df.loc[idx, "total"] = total
                    for k, v in signal_use_material.items():
                        result_df.loc[idx, k] = v
            update_plan_state(plan_id, 2)
            cols = result_df.columns.tolist()
            new_cols = []
            for col in cols:
                new_cols.append(str(col))
            result_df.columns = new_cols
            result_df["e_sort"] = e_sort
            result_df["plan_id"] = plan_id
            result_df["plan_name"] = plan.plan_name
            if old_df is not None:
                result_df = pd.concat([old_df, result_df])
            pandas_to_sql(result_df, f"result_{plan_id}", if_exists="replace")
            # 更新剩余材料的数量
            dynamic_remain_mo = DynamicRemainMaterial.query.filter(DynamicRemainMaterial.plan_id == plan_id).first()
            dynamic_remain_mo.material_remain = json.dumps(material_all_dict, indent=4)
            all_execute_products = result_df["product_model"].tolist()
            for product in all_execute_products:
                if product not in execute_product_str:
                    execute_product_str += product_model + "->"
            dynamic_remain_mo.execute_product = execute_product_str
            db.session.commit()
        except Exception as e:
            print(e)
            update_plan_state(plan_id, 3, str(e))
            db.session.commit()
        print("执行完毕...")
